//
//  SpringDataBase.m
//  ProjectA
//
//  Created by 李赐岩 姜雪 张昕锐 on 15-3-9.
//  Copyright (c) 2015年 李赐岩 姜雪 张昕锐. All rights reserved.
//

#import "SeasonDataBase.h"

@implementation SeasonDataBase

+(instancetype)shareSpringDataBase
{
    
    static SeasonDataBase *springData = nil;
    if (springData == nil) {
        springData = [[SeasonDataBase alloc] init];
    }
    return springData;
    
}
static sqlite3 *db = nil;


- (void)openDB
{
    if (db != nil) {
        NSLog(@"春天数据库已经打开");
        return;
    }
    //创建数据库文件
    NSArray *array = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSString *filePath = [array lastObject];
    NSString *sqlitePath = [filePath stringByAppendingPathComponent:@"recipe.sqlite"];
    //打开数据库
    int result = sqlite3_open(sqlitePath.UTF8String, &db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }else{
        NSLog(@"数据库打开失败");
    }
    
    
    
}

// 创建菜品列表表

- (void)createTable
{
    
    NSString *createSql = @"CREATE TABLE IF NOT EXISTS myList(number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, img TEXT, food TEXT, id TEXT, message TEXT)";
    
    //执行sql语句
    int result = sqlite3_exec(db, createSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
    
}

//插入数据
- (void)insertSpringModel:(SpringModel *)spring
{
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO myList(name, img, food, id, message) VALUES('%@', '%@', '%@', '%@', '%@')", spring.name, spring.img, spring.food, spring.id, spring.message];
    
    
    
    //执行sql语句
    int result = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
    
}

//删除一个数据
- (void)deleteSpring:(NSInteger)number
{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM myList WHERE number = '%ld'", number];
    
    int result = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}

//查询所有数据
- (NSMutableArray *)selectSpring
{
    NSString *selectSql = @"SELECT * FROM myList";
    
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    
   self.dataBaseArray = [NSMutableArray array];
    
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
        
        //开始循环遍历查询数据库每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //当跟随指针指向那一行的数据符合查询条件,就在while里返回
            //逐列取出每一列的数据
            
            int number = sqlite3_column_int(stmt, 0);   //column是列的意思      参数的意思是遍历第0列  类型为int型
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *img = sqlite3_column_text(stmt, 2);
            const unsigned char *food = sqlite3_column_text(stmt, 3);
            const unsigned char *id = sqlite3_column_text(stmt, 4);
            const unsigned char *message = sqlite3_column_text(stmt, 5);
            
            
            NSInteger numberInteger = number;
            
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *imglStr = [NSString stringWithUTF8String:(const char *)img];
            NSString *foodStr = [NSString stringWithUTF8String:(const char *)food];
            NSString *idStr = [NSString stringWithUTF8String:(const char *)id];
            NSString *messageStr = [NSString stringWithUTF8String:(const char *)message];
            
            SpringModel *spring = [[SpringModel alloc] init];
            spring.name = nameStr;
            spring.img = imglStr;
            spring.food = foodStr;
            spring.id = idStr;
            spring.message = messageStr;
            spring.number = numberInteger;
            
            //将学生对象存入大数组中
            [self.dataBaseArray addObject:spring];
            
        }
        
    }else{
        NSLog(@"查询准备失败");
    }
    
    return self.dataBaseArray;
    
}

-(void)deleteTable
{
    NSString *deleteTabel = @"DROP TABLE myList";
    
    int result = sqlite3_exec(db, deleteTabel.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"成功");
    }else
    {
        NSLog(@"失败");
    }
    
    
}







@end
